package com.lizard.schedule.persistence.db.table;

import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.text.TextUtils;

import com.lizard.schedule.log.ScheduleLog;
import com.lizard.schedule.net.http.request.bean.ScheduleSync.ScheduleSyncItem;
import com.lizard.schedule.persistence.db.DbHelper;
import com.lizard.schedule.persistence.db.bean.Schedule;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;

/**
 * 计划表
 * 计划表采用分表的方式，一个用户对应一张表，非注册用户使用单独一张表
 */
public class ScheduleTbl {

    private final String TAG = Schedule.class.getSimpleName();

    private final boolean LOG_ENABLE = true;

    private static ScheduleTbl instance;

    /**
     * 非登录用户的表名
     */
    private final String TBL_NAME = "a_tbl";

    /**
     * 表字段：主键
     */
    private final String FIELD_ID = "_id";

    /**
     * 表字段：本地id
     */
    private final String FIELD_LOCAL_ID = "f_a";

    /**
     * 表字段：文本内容
     */
    private final String FIELD_CONTENT_TEXT = "f_b";

    /**
     * 表字段：在server中的id
     */
    private final String FIELD_SERVER_ID = "f_c";

    /**
     * 表字段：开始时间
     */
    private final String FIELD_BEGIN_TIME = "f_d";

    /**
     * 表字段：状态
     */
    private final String FIELD_STATUS = "f_e";

    /**
     * 表字段：是否删除
     */
    private final String FIELD_DELETED = "f_f";

    /**
     * 表字段：是否已经提交最新修改到server
     */
    private final String FIELD_SUBMITED = "f_g";

    /**
     * 表字段：重复
     */
    private final String FIELD_REPEAT = "f_h";

    public static ScheduleTbl getInstance() {
        if (instance == null) {
            synchronized (ScheduleTbl.class) {
                if (instance == null) {
                    instance = new ScheduleTbl();
                }
            }
        }
        return instance;
    }

    /**
     * 删除存在于serverIds里面的对应数据
     *
     * @param usernameMD5L16
     * @param localIds
     */
    public void delete(String usernameMD5L16, List<Integer> localIds){
        if(localIds == null || localIds.size() <= 0){
            return;
        }
        String tblName = getTableName(usernameMD5L16);
        StringBuilder localIdsBuilder = new StringBuilder();
        for(int i = 0; i < localIds.size(); i++){
            if(i != 0){
                localIdsBuilder.append(",");
            }
            localIdsBuilder.append(localIds.get(i));
        }
        StringBuilder sqlBuilder = new StringBuilder();
        sqlBuilder.append("DELETE FROM ").append(tblName)
                .append(" WHERE ").append(FIELD_LOCAL_ID).append(" IN (").append(localIdsBuilder.toString()).append(");");
        DbHelper.getInstance().getSqliteDatabase().execSQL(sqlBuilder.toString());
    }

    /**
     * 统计未完成的历史计划数
     *
     * @param usernameMd5L16    若是非注册用户则传入null
     * @param currentTime       当前时间
     * @return
     */
    public int countUnfinishedHistoryScheduleCount(String usernameMd5L16, long currentTime){
        int count = 0;
        String tblName = getTableName(usernameMd5L16);
        StringBuilder sqlBuilder = new StringBuilder();
        sqlBuilder.append("SELECT count(1) FROM ").append(tblName)
                .append(" WHERE ").append(FIELD_BEGIN_TIME).append(" <= ? ")
                .append(" AND ").append(FIELD_STATUS).append(" = ").append(Schedule.STATUS_UNFINISHED)
                .append(" AND ").append(FIELD_DELETED).append(" = 0 ;");
        Cursor cursor = DbHelper.getInstance().getSqliteDatabase().rawQuery(sqlBuilder.toString(), new String[]{currentTime + ""});
        if(cursor.moveToNext()){
            count = cursor.getInt(0);
        }
        cursor.close();

        return count;
    }

    /**
     * 批量更新serverId
     *
     * @param usernameMd5L16
     * @param serverIdMap <localId, serverId>
     */
    public void batchUpdateServerId(String usernameMd5L16, Map<Integer, Long> serverIdMap){
        if(serverIdMap == null || serverIdMap.size() <= 0){
            return;
        }
        String tblName = getTableName(usernameMd5L16);
        DbHelper.getInstance().getSqliteDatabase().beginTransaction();
        for(Map.Entry<Integer, Long> entry: serverIdMap.entrySet()){
            StringBuilder sqlBuilder = new StringBuilder();
            sqlBuilder.append("UPDATE ").append(tblName)
                    .append(" SET ").append(FIELD_SERVER_ID).append(" = ? ")
                    .append(" WHERE ").append(FIELD_LOCAL_ID).append(" = ? ;");
            DbHelper.getInstance().getSqliteDatabase().execSQL(sqlBuilder.toString(), new Object[]{entry.getValue(), entry.getKey()});
        }
        DbHelper.getInstance().getSqliteDatabase().setTransactionSuccessful();
        DbHelper.getInstance().getSqliteDatabase().endTransaction();
    }

    /**
     * 设置计划提交状态为“已提交”
     *
     * @param usernameMd5L16
     * @param localIds
     */
    public void setSchedulesSubmited(String usernameMd5L16, List<Integer> localIds){
        if(localIds == null || localIds.size() <= 0){
            return;
        }
        String tblName = getTableName(usernameMd5L16);

        StringBuilder localIdsBuilder = new StringBuilder();
        for(int i = 0; i < localIds.size(); i++){
            if(i != 0){
                localIdsBuilder.append(",");
            }
            localIdsBuilder.append(localIds.get(i));
        }

        StringBuilder sqlBuilder = new StringBuilder();
        sqlBuilder.append("UPDATE ").append(tblName)
                .append(" SET ").append(FIELD_SUBMITED).append(" = 1 WHERE ")
                .append(FIELD_LOCAL_ID).append(" IN (").append(localIdsBuilder.toString()).append(");");
        DbHelper.getInstance().getSqliteDatabase().execSQL(sqlBuilder.toString());
    }

    /**
     * 查询未同步到server的“计划”集合
     *
     * @param usernameMd5L16
     * @return
     */
    public List<Schedule> selectUnsubmitedSchedules(String usernameMd5L16){
        List<Schedule> schedules = new ArrayList<Schedule>();
        String tblName = getTableName(usernameMd5L16);
        StringBuilder sqlBuilder = new StringBuilder();
        sqlBuilder.append("SELECT * FROM ").append(tblName)
                .append(" WHERE ").append(FIELD_SUBMITED).append(" = 0;");
        Cursor cursor = DbHelper.getInstance().getSqliteDatabase().rawQuery(sqlBuilder.toString(), null);
        while(cursor.moveToNext()){
            Schedule schedule = cursorToSchedule(cursor);
            schedules.add(schedule);
        }
        cursor.close();
        return schedules;
    }

    /**
     * 查询存在在serverIds里面的“计划”集合
     *
     * @param usernameMd5L16
     * @param serverIds
     * @return Map<serverId,Schedule>
     */
    public Map<Long, Schedule> selectByServerIds(String usernameMd5L16, List<Long> serverIds){
        Map<Long, Schedule> schedules = new HashMap<Long, Schedule>();
        String tblName = getTableName(usernameMd5L16);
        StringBuilder serverIdsBuilder = new StringBuilder();
        for(int i = 0; i < serverIds.size(); i++){
            if(i != 0){
                serverIdsBuilder.append(",");
            }
            serverIdsBuilder.append("'").append(serverIds.get(i)).append("'");
        }
        StringBuilder sqlBuilder = new StringBuilder();
        sqlBuilder.append("SELECT * FROM ").append(tblName)
                .append(" WHERE ").append(FIELD_SERVER_ID).append(" IN (").append(serverIdsBuilder.toString()).append(");");
        Cursor cursor = DbHelper.getInstance().getSqliteDatabase().rawQuery(sqlBuilder.toString(), null);
        while(cursor.moveToNext()){
            Schedule schedule = cursorToSchedule(cursor);
            schedules.put(schedule.getServerId(), schedule);
        }
        cursor.close();
        return schedules;
    }

    /**
     * 查询所有“重复”的计划
     *
     * @param usernameMd5L16
     * @return
     */
    public List<Schedule> selectRepeatSchedules(String usernameMd5L16){
        List<Schedule> schedules = new ArrayList<Schedule>();
        String tblName = getTableName(usernameMd5L16);
        StringBuilder sqlBuilder = new StringBuilder();
        sqlBuilder.append("SELECT * FROM ").append(tblName)
                .append(" WHERE ").append(FIELD_REPEAT).append(" != ?;");
        Cursor cursor = DbHelper.getInstance().getSqliteDatabase().rawQuery(sqlBuilder.toString(),
                new String[]{ScheduleSyncItem.REPEAT_NONE + ""});
        while(cursor.moveToNext()){
            Schedule schedule = cursorToSchedule(cursor);
            schedules.add(schedule);
        }
        cursor.close();
        return schedules;
    }

    /**
     * 根据locakId搜索schedule
     *
     * @param usernameMd5L16
     * @param localId
     * @return
     */
    public Schedule selectByLocalId(String usernameMd5L16, int localId){
        Schedule schedule = null;
        String tblName = getTableName(usernameMd5L16);
        StringBuilder sqlBuilder = new StringBuilder();
        sqlBuilder.append("SELECT * FROM ").append(tblName)
                .append(" WHERE ").append(FIELD_LOCAL_ID).append(" = ?;");
        Cursor cursor = DbHelper.getInstance().getSqliteDatabase().rawQuery(sqlBuilder.toString(),
                new String[]{localId + ""});
        if(cursor.moveToNext()){
            schedule = cursorToSchedule(cursor);
        }
        cursor.close();
        return schedule;
    }

    /**
     * 查询"计划"集合
     *
     * @param usernameMd5L16    若是非注册用户则传入null
     * @param localId           查询条件起始的localId(不包含该条localId), 无该条件则传0
     * @param count             查询的数量
     * @param time              查询条件的起始时间(不包含)
     * @param future            true: 查询未来的数据并小到大排序 false：查询历史数据并大到小排序
     * @param includeDeleted    是否包含标记为删除的数据
     * @return
     */
    public List<Schedule> select(String usernameMd5L16, int localId, int count, long time, boolean future, boolean includeDeleted) {

        // 用于查询速度计时
        long begin;
        if(LOG_ENABLE){
            begin = System.currentTimeMillis();
        }

        List<Schedule> schedules = new ArrayList<Schedule>();
        String tblName = getTableName(usernameMd5L16);
        StringBuilder sqlBuilder = new StringBuilder();
        sqlBuilder.append("SELECT * FROM ").append(tblName).append(" WHERE ((")
                .append(FIELD_BEGIN_TIME).append(" = ? AND ")
                .append(FIELD_LOCAL_ID).append(future ? " > " : " < ").append("? ) OR ")
                .append(FIELD_BEGIN_TIME).append(future ? " > " : " < ").append("? )");

        if(!includeDeleted){
            sqlBuilder.append(" AND ").append(FIELD_DELETED).append(" = 0 ");
        }

        sqlBuilder.append(" ORDER BY ").append(FIELD_BEGIN_TIME).append(future ? " ASC " : " DESC ")
                .append(", ").append(FIELD_LOCAL_ID).append(future ? " ASC " : " DESC ")
                .append("LIMIT 0,? ;");

        if(LOG_ENABLE){
            ScheduleLog.v(TAG, sqlBuilder.toString());
        }

        Cursor cursor = DbHelper.getInstance().getSqliteDatabase().rawQuery(sqlBuilder.toString(),
                new String[]{
                        time + "",
                        localId + "",
                        time + "",
                        count + ""
                });
        while (cursor.moveToNext()) {
            Schedule schedule = cursorToSchedule(cursor);
            schedules.add(schedule);

        }
        cursor.close();

        if(LOG_ENABLE){
            ScheduleLog.v(TAG, "select sql used time:" + (System.currentTimeMillis() - begin));
        }

        return schedules;
    }

    /**
     * cursor数据取出到Schedule对象
     *
     * @param cursor
     * @return
     */
    private Schedule cursorToSchedule(Cursor cursor){
        Schedule schedule = new Schedule(
                cursor.getInt(cursor.getColumnIndex(FIELD_LOCAL_ID)),
                cursor.getString(cursor.getColumnIndex(FIELD_CONTENT_TEXT)),
                cursor.getLong(cursor.getColumnIndex(FIELD_SERVER_ID)),
                cursor.getLong(cursor.getColumnIndex(FIELD_BEGIN_TIME)),
                cursor.getInt(cursor.getColumnIndex(FIELD_STATUS)),
                cursor.getInt(cursor.getColumnIndex(FIELD_DELETED)) == 1 ? true : false,
                cursor.getInt(cursor.getColumnIndex(FIELD_SUBMITED)) == 1 ? true : false,
                cursor.getInt(cursor.getColumnIndex(FIELD_REPEAT))
        );
        return schedule;
    }

    /**
     * 批量添加数据
     *
     * @param usernameMd5L16
     * @param schedules
     */
    public void batchInsert(String usernameMd5L16, List<Schedule> schedules){
        if(schedules == null || schedules.size() <= 0){
            return;
        }
        DbHelper.getInstance().getSqliteDatabase().beginTransaction();
        for(Schedule schedule: schedules){
            insert(usernameMd5L16, schedule);
        }
        DbHelper.getInstance().getSqliteDatabase().setTransactionSuccessful();
        DbHelper.getInstance().getSqliteDatabase().endTransaction();
    }

    /**
     * 添加数据
     *
     * @param usernameMd5L16 若是非注册用户则传入null
     * @param schedule
     */
    public void insert(final String usernameMd5L16, final Schedule schedule) {
        String tblName = getTableName(usernameMd5L16);
        StringBuilder sqlBuilder = new StringBuilder();
        sqlBuilder.append("INSERT INTO ").append(tblName).append("(")
                .append(FIELD_LOCAL_ID).append(",")
                .append(FIELD_CONTENT_TEXT).append(",")
                .append(FIELD_SERVER_ID).append(",")
                .append(FIELD_BEGIN_TIME).append(",")
                .append(FIELD_STATUS).append(",")
                .append(FIELD_DELETED).append(",")
                .append(FIELD_SUBMITED).append(",")
                .append(FIELD_REPEAT)
                .append(") VALUES(?,?,?,?,?,?,?,?);");
        DbHelper.getInstance().getSqliteDatabase().execSQL(sqlBuilder.toString(),
                new Object[]{
                        schedule.getLocalId(),
                        schedule.getContentText(),
                        schedule.getServerId(),
                        schedule.getBeginTime(),
                        schedule.getStatus(),
                        schedule.isDeleted(),
                        schedule.isSubmited(),
                        schedule.getRepeat()
                });
    }

    /**
     * 批量更新
     *
     * @param usernameMd5L16
     * @param schedules
     */
    public void batchUpdate(String usernameMd5L16, List<Schedule> schedules){
        if(schedules == null || schedules.size() <= 0){
            return;
        }
        DbHelper.getInstance().getSqliteDatabase().beginTransaction();
        for(Schedule schedule: schedules){
            update(usernameMd5L16, schedule);
        }
        DbHelper.getInstance().getSqliteDatabase().setTransactionSuccessful();
        DbHelper.getInstance().getSqliteDatabase().endTransaction();
    }

    /**
     * 更新数据
     *
     * @param usernameMd5L16 若是非注册用户则传入null
     * @param schedule
     */
    public void update(String usernameMd5L16, Schedule schedule){
        String tblName = getTableName(usernameMd5L16);
        StringBuilder sqlBuilder = new StringBuilder();
        sqlBuilder.append("UPDATE ").append(tblName).append(" SET ")
                .append(FIELD_CONTENT_TEXT).append(" = ?,")
                .append(FIELD_SERVER_ID).append(" = ?,")
                .append(FIELD_BEGIN_TIME).append(" = ?,")
                .append(FIELD_STATUS).append(" = ?,")
                .append(FIELD_DELETED).append(" = ?,")
                .append(FIELD_SUBMITED).append(" = ?,")
                .append(FIELD_REPEAT).append(" = ?")
                .append(" WHERE ")
                .append(FIELD_LOCAL_ID).append(" = ?;");
        DbHelper.getInstance().getSqliteDatabase().execSQL(sqlBuilder.toString(),
                new Object[]{
                        schedule.getContentText(),
                        schedule.getServerId(),
                        schedule.getBeginTime(),
                        schedule.getStatus(),
                        schedule.isDeleted(),
                        schedule.isSubmited(),
                        schedule.getRepeat(),
                        schedule.getLocalId()
                });

    }

    /**
     * 创建登录用户的计划表
     *
     * @param usernameMd5L16 如果是非注册用户则传入null
     * @param db
     */
    public void createTbl(String usernameMd5L16, SQLiteDatabase db) {

        StringBuilder sqlBuilder = new StringBuilder();
        sqlBuilder.append("CREATE TABLE IF NOT EXISTS ").append(getTableName(usernameMd5L16)).append("(")
                .append(FIELD_ID).append(" INTEGER PRIMARY KEY AUTOINCREMENT,")
                .append(FIELD_LOCAL_ID).append(" INTEGER,")
                .append(FIELD_CONTENT_TEXT).append(" TEXT,")
                .append(FIELD_SERVER_ID).append(" TEXT,")
                .append(FIELD_BEGIN_TIME).append(" TEXT,")
                .append(FIELD_STATUS).append(" INTEGER,")
                .append(FIELD_DELETED).append(" INTEGER,")
                .append(FIELD_SUBMITED).append(" INTEGER,")
                .append(FIELD_REPEAT).append(" INTEGER")
                .append(");");
        db.execSQL(sqlBuilder.toString());
    }

    /**
     * 创建表
     *
     * @param usernameMd5L16
     */
    public void createTbl(String usernameMd5L16) {
        createTbl(usernameMd5L16, DbHelper.getInstance().getSqliteDatabase());
    }

    /**
     * 删除表所有计划表(非注册用户、所有注册用户)
     *
     * @param db
     */
    public void dropTbl(SQLiteDatabase db) {
        Set<String> tblNames = searchAllExistScheduleTblNames(db);
        for (String name : tblNames) {
            StringBuilder sqlBuilder = new StringBuilder();
            sqlBuilder.append("DROP TABLE IF EXISTS ").append(name);
            db.execSQL(sqlBuilder.toString());
        }
    }

    /**
     * 搜索所有的计划表的表名(非注册用户、所有注册用户)
     *
     * @param db
     * @return
     */
    private HashSet<String> searchAllExistScheduleTblNames(SQLiteDatabase db) {
        HashSet<String> tblNames = new HashSet<String>();
        StringBuilder sqlBuilder = new StringBuilder();
        sqlBuilder.append("SELECT name FROM sqlite_master WHERE name LIKE '")
                .append(TBL_NAME).append("%'");
        Cursor cursor = db.rawQuery(sqlBuilder.toString(), null);
        while (cursor.moveToNext()) {
            String name = cursor.getString(cursor.getColumnIndex("name"));
            if (name != null) {
                tblNames.add(name);
            }
        }
        cursor.close();
        return tblNames;
    }

    /**
     * 新增列：重复
     * @param db
     */
    public void addColumnRepeat(SQLiteDatabase db){
        Set<String> tblNames = searchAllExistScheduleTblNames(db);
        for (String name : tblNames) {
            StringBuilder sqlBuilder = new StringBuilder();
            sqlBuilder.append("ALTER TABLE ").append(name).append(" ADD ")
                    .append(FIELD_REPEAT).append(" INTEGER DEFAULT 0");
            db.execSQL(sqlBuilder.toString());
        }

    }

    /**
     * 获取表名
     *
     * @param usernameMD5L16 用户名16位md5值
     * @return
     */
    private String getTableName(String usernameMD5L16) {
        if (TextUtils.isEmpty(usernameMD5L16)) {    // 非注册用户
            return TBL_NAME;
        } else {
            return TBL_NAME + usernameMD5L16;
        }
    }

}
